﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Runda.CMS.Common.Entity;


namespace Runda.CMS.Dal
{
    public partial class SysDepartmentDal
    {
        /// <summary>
        /// 根据用户构造部门树
        /// </summary>
        /// <param name="RoleID"></param>
        /// <returns></returns>
        public DataTable BuildAllDepartment(Guid UserID)
        {
            string sql = @"select A.*, 
case when B.UserID IS NULL then 0 else 1 end as IsChecked
from Cms_Sys_Department A
left join Cms_Sys_UserDepartment B ON A.DepartmentID = B.DepartmentID and B.UserID =@UserID
";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@UserID", DbType.Guid, UserID);

            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        //数据权限-部门
        public DataTable BuildAllDepartmentByDataRight(Guid UserID)
        {
            string sql = @"select A.*, 
case when B.UserID IS NULL then 0 else 1 end as IsChecked
from Cms_Sys_Department A
left join Cms_Sys_UserDepartment_DataRight B ON A.DepartmentID = B.DepartmentID and B.UserID =@UserID
";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@UserID", DbType.Guid, UserID);

            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        //数据权限-产品
        public DataTable BuildAllProductCategory(Guid UserID)
        {
            string sql = @"select a.categoryGUID,a.parentGUID,a.categoryCode,a.categoryName, 
case when B.UserID IS NULL then 0 else 1 end as IsChecked
from Cms_Ctrt_InstrumentCategory A
left join Cms_sys_UserProductCategory B ON A.categoryGUID = B.categoryGUID and B.UserID =@UserID
where LEN(A.categoryCode) <=6 order by a.categoryCode";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@UserID", DbType.Guid, UserID);

            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        public DataTable BuildAllProductCategory()
        {
            string sql = @"select a.categoryGUID,a.parentGUID,a.categoryCode,a.categoryName
from Cms_Ctrt_InstrumentCategory A
where categoryName not like '%折扣%' and LEN(A.categoryCode) <=6 order by a.categoryCode";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }


        public List<SysDepartmentEntity> GetParentDepartmentEntities(Guid DepartmentGUID)
        {
            List<SysDepartmentEntity> entities = null;
            string sql = @"with CTE as
            (
            select * from Cms_Sys_Department where DepartmentID = @DepartmentGUID

            union all

            select A.* from Cms_Sys_Department A join CTE on A.DepartmentID = CTE.ParentID
            )
            select * from CTE";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@DepartmentGUID", System.Data.DbType.Guid, DepartmentGUID);
            using (IDataReader reader = CurrentDatabase.ExecuteReader(dbcommand))
            {
                entities = GetListFromReader<SysDepartmentEntity>(reader);
            }
            return entities;
        }

        public List<SysDepartmentEntity> GetChildDepartmentEntities(Guid DepartmentGUID)
        {
            List<SysDepartmentEntity> entities = null;
            string sql = @"with CTE as
            (
            select * from Cms_Sys_Department where DepartmentID = @DepartmentGUID

            union all

            select A.* from Cms_Sys_Department A join CTE on A.ParentID = CTE.DepartmentID
            )
            select * from CTE";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@DepartmentGUID", System.Data.DbType.Guid, DepartmentGUID);
            using (IDataReader reader = CurrentDatabase.ExecuteReader(dbcommand))
            {
                entities = GetListFromReader<SysDepartmentEntity>(reader);
            }
            return entities;
        }

    }
}
